﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using EntitiesLayer.Entities;
using System.Data;

namespace DataLayer.DAO
{
    public class CorreoDAO
    {
        private Connection.Connection cn;
        private OracleConnection objCn;

        public CorreoDAO()
        {
            cn = new Connection.Connection();
            objCn = cn.Conecta();
        }

        public Correo consultaTipoCorreo(string codTipCorreo)
        {
            Correo p = new Correo();

            using (OracleCommand cmd = new OracleCommand("ENVIARCORREO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter refParameter = new OracleParameter();
                refParameter.ParameterName = "RECORDSET";
                refParameter.OracleType = OracleType.Cursor;
                refParameter.Direction = ParameterDirection.Output;

                OracleParameter correoParameter = new OracleParameter();
                correoParameter.ParameterName = "R_CODTIPO_CORREO";
                correoParameter.OracleType = OracleType.VarChar;
                correoParameter.Size = 20;
                correoParameter.Direction = ParameterDirection.Input;
                correoParameter.Value = codTipCorreo.Trim().ToLower();

                cmd.Parameters.Add(refParameter);
                cmd.Parameters.Add(correoParameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        p.TituloCorreo = Convert.ToString(dr[0]);
                        p.DetalleCorreo = Convert.ToString(dr[1]);
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return p;


        }
        public String DestinatariosSugerencia(string Rare_Cod_Area)
        {
            String salida = "";

            using (OracleCommand cmd = new OracleCommand("NOTI_DESC_SUGERENCIA", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter refParameter = new OracleParameter();
                refParameter.ParameterName = "RECORDSET";
                refParameter.OracleType = OracleType.Cursor;
                refParameter.Direction = ParameterDirection.Output;

                OracleParameter Rare_Cod_AreaParameter = new OracleParameter();
                Rare_Cod_AreaParameter.ParameterName = "Rare_Cod_Area";
                Rare_Cod_AreaParameter.OracleType = OracleType.VarChar;
                Rare_Cod_AreaParameter.Size = 20;
                Rare_Cod_AreaParameter.Direction = ParameterDirection.Input;
                Rare_Cod_AreaParameter.Value = Rare_Cod_Area;

                cmd.Parameters.Add(refParameter);
                cmd.Parameters.Add(Rare_Cod_AreaParameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();
                    int i = 0;
                    while (dr.Read())
                    {
                        if (i > 0)
                        {
                            salida = salida + "," + Convert.ToString(dr[0]);
                        }
                        else
                        {
                            salida = Convert.ToString(dr[0]);
                        }
                        i++;
                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return salida;


        }

    }
}
